# 01_rfm_analysis.py - RFM用户价值分群分析
import pandas as pd
import sqlite3


def rfm_analysis(csv_file_path):
    """
    RFM用户分群分析
    输入: CSV文件路径
    输出: RFM分析结果的CSV文件
    """
    print("=== 开始RFM用户分群分析 ===")

    # 读取数据
    df = pd.read_csv(csv_file_path)
    conn = sqlite3.connect(':memory:')
    df.to_sql('user_personalized_features', conn, index=False, if_exists='replace')

    # 详细的RFM分析SQL
    rfm_detailed_sql = """
    WITH RFM_Base AS (
        SELECT
            User_ID,
            Age,
            Gender,
            Location,
            Income,
            Interests,
            Last_Login_Days_Ago AS Recency,
            Purchase_Frequency AS Frequency,
            Total_Spending AS Monetary,
            Average_Order_Value,
            Product_Category_Preference,
            Time_Spent_on_Site_Minutes,
            Pages_Viewed,
            Newsletter_Subscription
        FROM user_personalized_features
    ),
    RFM_Score AS (
        SELECT
            *,
            NTILE(4) OVER (ORDER BY Recency ASC) AS R_Score,
            NTILE(4) OVER (ORDER BY Frequency DESC) AS F_Score,
            NTILE(4) OVER (ORDER BY Monetary DESC) AS M_Score
        FROM RFM_Base
    ),
    RFM_Group AS (
        SELECT
            *,
            CONCAT(R_Score, F_Score, M_Score) AS RFM_Cell,
            CASE 
                WHEN R_Score >= 3 AND F_Score >= 3 AND M_Score >= 3 THEN '高价值用户'
                WHEN R_Score >= 3 AND F_Score >= 3 THEN '频繁用户'
                WHEN R_Score >= 3 AND M_Score >= 3 THEN '高消费用户'
                WHEN R_Score >= 3 THEN '新用户'
                WHEN F_Score >= 3 AND M_Score >= 3 THEN '需唤回用户'
                WHEN F_Score >= 3 THEN '需促活用户'
                WHEN M_Score >= 3 THEN '需挽留用户'
                ELSE '流失用户'
            END AS User_Segment
        FROM RFM_Score
    )
    SELECT * FROM RFM_Group
    """

    # 执行详细分析
    print("正在执行RFM分析...")
    detailed_result = pd.read_sql_query(rfm_detailed_sql, conn)

    # 生成汇总统计（使用正确的列名）
    print("生成汇总统计...")
    summary_result = detailed_result.groupby('User_Segment').agg({
        'User_ID': 'count',
        'Monetary': ['mean', 'sum'],
        'Frequency': 'mean',
        'Recency': 'mean'
    }).reset_index()

    # 重命名列
    summary_result.columns = ['用户分群', '用户数量', '平均消费金额', '总消费金额', '平均购买频率', '平均未登录天数']

    # 计算占比百分比
    total_users = len(detailed_result)
    summary_result['占比百分比'] = round((summary_result['用户数量'] / total_users) * 100, 2)

    # 重新排列列顺序
    summary_result = summary_result[
        ['用户分群', '用户数量', '占比百分比', '平均消费金额', '平均购买频率', '平均未登录天数', '总消费金额']]

    # 按用户数量排序
    summary_result = summary_result.sort_values('用户数量', ascending=False)

    # 保存结果
    detailed_result.to_csv('rfm_detailed_analysis.csv', index=False, encoding='utf-8-sig')
    summary_result.to_csv('rfm_summary_analysis.csv', index=False, encoding='utf-8-sig')

    print("RFM分析完成！")
    print(f"用户分群分布:")
    print(summary_result.to_string(index=False))

    print(f"\n结果文件已保存:")
    print("- rfm_detailed_analysis.csv (详细分群数据)")
    print("- rfm_summary_analysis.csv (汇总统计数据)")

    conn.close()
    return detailed_result, summary_result


if __name__ == "__main__":
    # 使用示例
    rfm_analysis('user_personalized_features.csv')